#!/usr/bin/env python


import sys
from config import *

# ping proposaldb server, get all new users since given date/time,
from ZSI import FaultException


#populate remote ICAT FACILITY_USER table with these users
print "Connecting: ICAT Oracle Database"
from xmlMapping.dbConnect import *

bypass=False
try:
  dbcnx = DBConnection(bypass)
  dbcnx.cxInit(ICATDBHOST, ICATDBPORT, ICATDBNAME, ICATDBUSER, ICATDBPASS)
  dbcnx.connect()     # establish tcp communication
except Exception, e:
  print e
  print "You may need to port forward over SSH to tunnel through firewall"
  print "e.g.   ssh dataminx -L 1521:localhost:1521"
  sys.exit(1)
print "Connected to ",ICATDBUSER 

def oracleEscape(strn):
  if not strn: return ''
  if strn=='none': return ''
  if strn=='None': return ''
  out = []
  for char in strn:
    if char =="'":
      out.append("'")
      out.append(char)
    else:
      out.append(char)
  ret = "".join(out)
  return ret


DBSCHEMA="ICAT"
TABLE = 'DATAFILE_FORMAT'
try:
    columns =dbcnx.query("""SELECT column_name FROM COLS
          WHERE table_name='%(TABLE)s'""" % 
              {'DBSCHEMA' : DBSCHEMA, 'TABLE' : TABLE } )
except Exception, msg:
    import traceback
    traceback.print_exc(file=sys.stdout)
    print "Select header from ", TABLE, " failed\n%s" % msg,
    sys.exit()

header = []
if not columns or columns.ntuples < 1:
  print " no column headers exist"
else:
  for row in columns:
    header.append(row[0])

fileformats = []
try:
    fields = ", ".join(header)
#    print fields
    oldformats = dbcnx.query("""SELECT %(fields)s  FROM %(TABLE)s """ % 
              {'DBSCHEMA' : DBSCHEMA ,'fields' : fields, 'TABLE' : TABLE } )
except Exception, msg:
    import traceback
    traceback.print_exc(file=sys.stdout)
    print "Select from datafile_format failed\n%s" % msg,
    sys.exit()
if not oldformats or oldformats.ntuples < 1:
    print " no file format entries exist"
else:
    for row in oldformats:
#      print row
      d = {}
      for i in range(len(header)):
        d[header[i]] = row[i] 
      fileformats.append(d)


def injectNewFileFormat(format, version):
  try:
#   (NAME, VERSION, FORMAT_TYPE, DESCRIPTION, SEQ_NUMBER, MOD_TIME, MOD_ID, CREATE_TIME, CREATE_ID, FACILITY_ACQUIRED, DELETED)
# Values
#   ('nexus', '3.0.0', 'HDF5', 'Neutron and X-Ray data format.', 999, TO_TIMESTAMP('12/09/2007 13:30:16.4','DD/MM/YYYY HH24:MI:SS.FF'), 'overlord', TO_TIMESTAMP('12/09/2007 13:30:16.4','DD/MM/YYYY HH24:MI:SS.FF'), 'overlord', 'Y', 'N');

    query= """
       INSERT INTO DATAFILE_FORMAT (NAME, VERSION, 
       FORMAT_TYPE, DESCRIPTION, SEQ_NUMBER, MOD_TIME, MOD_ID, 
       CREATE_TIME, CREATE_ID, FACILITY_ACQUIRED, DELETED) VALUES 
       ('%(name)s', '%(version)s', '%(type)s', '%(description)s', 
         999, systimestamp, 'overlord', systimestamp,'overlord', 'Y', 'N') """ % \
      {'DBSCHEMA' : DBSCHEMA, 
       'name': oracleEscape(format['format']), 
       'version': oracleEscape(version), 
       'type': oracleEscape(format['format_type']), 
       'description':oracleEscape(format['description']),  
      }  # )
    print query
    res = dbcnx.query(query)
    res = dbcnx.query("COMMIT")
  except Exception, msg:
    print "Failed to inject new  dataformat: ", format
    print  msg
    sys.exit()
  return
     

for code in DataFormats.FILETYPES:
  ftype = DataFormats.FILETYPES[code] 
  versions = ftype['version']
  if versions:
    for version in versions:
      match = False
      for itype in fileformats:
        if itype['NAME'] != ftype['format']: continue
        if itype['VERSION'] != version: continue
        match = True
        break
      if match: continue
      print "No current ICAT match for" , ftype['format'], version
      print "Adding new entry ..."
      injectNewFileFormat(ftype,version)
       
  else:  # no version
      match = False
      for itype in fileformats:
        if itype['NAME'] != ftype['format']: continue
        match = True
        break
      if match: continue
      print "no match for" , ftype['format']
      injectNewFileFormat(ftype,'1')


print "ICAT datafile_formats and config.py FILETYPES are reconciled." 
print "Nothing else to add."
dbcnx.finish() 
